package approval.dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import model.pojo.Employee;
import model.pojo.ListApprove;

import models.util.MySqlDataAccessHelper;
import resource.pojo.Approvedoc;

public class ApproveDocumentDAO {
	public static boolean insert(Approvedoc app) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		int i = 0;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "call sp_insertDoc(?,?,?,?,?,?,?,?,?,?,?,?)";
		CallableStatement ps = null;
		try {
			mysql.open();
			// approvedoc(Tile,Content,IDEmployee,IDStatus,IDTypeDoc,comment,Description,purpose,Location,TripDate,TotalTime,EmployeePerforment)
			ps = mysql.getConnection().prepareCall(sql);
			ps.setString(1, app.getTitle());
			ps.setString(2, app.getContent());
			ps.setString(3, app.getiDEmployee());
			ps.setInt(4, 4);
			ps.setInt(5, app.getiDTypeDoc());
			ps.setString(6, app.getComment());
			ps.setString(7, app.getDescription());
			ps.setString(8, app.getPurpose());
			ps.setString(9, app.getLocation());
			if (app.getTripDate() == null) {
				ps.setString(10, null);
			} else {
				ps.setString(10, df.format(app.getTripDate()));
			}
			ps.setString(11, app.getTotalTime());
			ps.setString(12, app.getEmployeePerforment());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return i > 0;
	}

	public static boolean update(int id, String idGroup) {
		int a = 0;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = String
				.format("UPDATE approvedoc SET IDGroupApprove='%s',IDStatus=2 WHERE IDApproveDoc=%s",
						idGroup, id);
		try {
			mysql.open();
			a = mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return a > 0;
	}

	public static boolean updateMeeting(int id) {
		int a = 0;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = String.format(
				"UPDATE approvedoc SET IDStatus=2 WHERE IDApproveDoc=%s", id);
		try {
			mysql.open();
			a = mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return a > 0;
	}

	// / document sending
	public static List<Approvedoc> getAllDoc(int status, String idGroup,
			String title, String idEmp, String databasename) {
		/*
		 * System.out.println("123"); System.out.println("Tile:"+title);
		 * System.out.println("Tile:"+idEmp);
		 */
		List<Approvedoc> lst = new ArrayList<Approvedoc>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try {
			mysql.open(databasename);
			ResultSet rs = mysql.executeQuery(String.format(
					"call sp_getAllDoc(%d,'%s','%s','%s')", status, idGroup,
					title, idEmp));

			while (rs.next()) {
				if (rs.getInt("IDTypeDoc") != 11) {
					lst.add(new Approvedoc(rs.getString("IDApproveDoc"), rs
							.getString("Tile"), rs.getString("Content"), rs
							.getString("IDEmployee"), rs
							.getString("IDGroupApprove"), rs
							.getTimestamp("Timecreate"), rs.getInt("IDStatus"),
							rs.getInt("IDTypeDoc"), rs
									.getTimestamp("TimeApprove"), rs
									.getString("comment"), rs
									.getString("Description"), rs
									.getString("purpose"), rs
									.getString("EmployeePerforment"), rs
									.getTimestamp("TripDate"), rs
									.getString("TotalTime"), rs
									.getString("Location")));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		// System.out.println("lst:"+lst.size());
		return lst;
	}

	// Lấy các văn bản chưa gửi phê duyệt
	public static ArrayList<Approvedoc> listStorage(String em,
			String databasename) {
		ArrayList<Approvedoc> lst = new ArrayList<Approvedoc>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try {

			Approvedoc approveDoc = null;
			mysql.open(databasename);
			String sql = "select * from approvedoc"
					+ " where ISNULL(IDGroupApprove)  "
					+ " and IDStatus =4 and IDEmployee='" + em + "' "
					+ "and (IDTypeDoc=12 or IDTypeDoc=14 or IDTypeDoc=16 )  "
					+ "order by approvedoc.IDApproveDoc desc";
			System.out.println("sqltypeDoc:" + sql);
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				approveDoc = new Approvedoc(rs.getString("IDApproveDoc"),
						rs.getString("Tile"), rs.getString("Content"),
						rs.getString("IDEmployee"),
						rs.getString("IDGroupApprove"),
						rs.getTimestamp("Timecreate"), rs.getInt("IDStatus"),
						rs.getInt("IDTypeDoc"), rs.getTimestamp("TimeApprove"),
						rs.getString("comment"), rs.getString("Description"),
						rs.getString("purpose"),
						rs.getString("EmployeePerforment"),
						rs.getTimestamp("TripDate"), rs.getString("TotalTime"),
						rs.getString("Location"));
				approveDoc.setContent2(rs.getString("Content2"));
				lst.add(approveDoc);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return lst;
	}

	// Lấy các văn bản chưa gửi phê duyệt van ban hop
	public static ArrayList<Approvedoc> listStorageMeeting(String em,
			String databasename) {
		ArrayList<Approvedoc> lst = new ArrayList<Approvedoc>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try {
			mysql.open(databasename);
			/*
			 * FacesContext f = FacesContext.getCurrentInstance(); HttpSession
			 * ss = (HttpSession) f.getExternalContext().getSession(true);
			 * Employee em = (Employee) ss.getAttribute("Employee");
			 */
			String sql = "SELECT * FROM approvedoc WHERE IDStatus=2 And IDTypeDoc = 11 and IDEmployee='"
					+ em + "'";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				lst.add(new Approvedoc(rs.getString("IDApproveDoc"), rs
						.getString("Tile"), rs.getString("Content"), rs
						.getString("IDEmployee"), rs
						.getString("IDGroupApprove"), rs
						.getTimestamp("Timecreate"), rs.getInt("IDStatus"), rs
						.getInt("IDTypeDoc"), rs.getTimestamp("TimeApprove"),
						rs.getString("comment"), rs.getString("Description")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return lst;
	}

	public static List<Employee> getNameEmployee(String databasename,
			String iDApproveDoc) {
		MySqlDataAccessHelper mysqlHelper = new MySqlDataAccessHelper();
		List<Employee> lst = new ArrayList<Employee>();
		String sql = "Select em.FullName from employee em,approvedoc app,listapprove lstApp where app.IDApproveDoc='"
				+ iDApproveDoc
				+ "' and lstApp.IDGroupApprove=app.IDGroupApprove and lstApp.IDEmployee=em.IDEmployee";
		ResultSet rs = null;
		String result = "";
		System.out.println("sql:" + sql);
		try {
			mysqlHelper.open(databasename);
			rs = mysqlHelper.executeQuery(sql);
			while (rs.next()) {
				lst.add(new Employee(rs.getString("FullName")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysqlHelper.close();
		}
		return lst;
	}

	/*
	 * public static List<Approvedoc> getAllDoc1(int status,String
	 * idGroup,String title,String idEmp,String databasename){ List<Approvedoc>
	 * lst = new ArrayList<Approvedoc>(); MySqlDataAccessHelper helper = new
	 * MySqlDataAccessHelper(); try { helper.open(databasename); String
	 * sql="select appDoc.IDApproveDoc,staLang.IDConfigStaticLang " +
	 * "from approvedoc appDoc,typedoc tyDoc,staticlang staLang " +
	 * "where appDoc.IDStatus='2'" +
	 * "and appDoc.IDEmployee like '%"+idEmp+"%' and appDoc.Tile like '%"
	 * +title+"%'" + "and appDoc.IDApproveDoc like '%"+idGroup+"%' " +
	 * "and staLang.IDConfigStaticLang=tyDoc.IDConfigStaticLang " +
	 * "and appDoc.IDTypeDoc=tyDoc.IDTypeDoc " +
	 * "ORDER BY appDoc.IDApproveDoc DESC"; helper.getConnection(); ResultSet
	 * rs=helper.executeQuery(sql); while (rs.next()) { Approvedoc appDoc = new
	 * Approvedoc();
	 * 
	 * 
	 * } } catch (Exception e) { e.printStackTrace(); } return lst; }
	 */

	public static ArrayList<Approvedoc> getDocReference(String id) {
		ArrayList<Approvedoc> lst = new ArrayList<Approvedoc>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		CallableStatement cs = null;
		String sql = "call sp_getDocReference(?)";
		try {
			mysql.open();
			cs = mysql.getConnection().prepareCall(sql);
			cs.setString(1, id);
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				lst.add(new Approvedoc(rs.getString("IDApproveDoc"), rs
						.getString("Tile"), rs.getString("Content"), rs
						.getString("IDEmployee"), rs
						.getString("IDGroupApprove"), rs
						.getTimestamp("Timecreate"), rs.getInt("IDStatus"), rs
						.getInt("IDTypeDoc"), rs.getTimestamp("TimeApprove"),
						rs.getString("comment"), rs.getString("Description"),
						rs.getString("purpose"), rs
								.getString("EmployeePerforment"), rs
								.getTimestamp("TripDate"), rs
								.getString("TotalTime"), rs
								.getString("Location")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return lst;
	}

	public Approvedoc getOnce16(String id, String db) {
		Approvedoc ap = new Approvedoc();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try {
			mysql.open(db);
			String sql = "select * from approvedoc where approvedoc.IDApproveDoc='"
					+ id + "'";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				ap.setiDApproveDoc(rs.getString("IDApproveDoc"));
				ap.setTitle(rs.getString("Tile"));
				ap.setContent(rs.getString("Content"));
				ap.setContent2(rs.getString("Content2"));
				ap.setTimecreate(rs.getDate("Timecreate"));
				ap.setDescription(rs.getString("Description"));
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}

		return ap;
	}

	public static void moveApproveToTrash(String id, String db) {
		System.out.println("id: "+id);
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try {
			mysql.open(db);
			String sql = "UPDATE approvedoc set IDStatus = 6 WHERE IDApproveDoc ="
					+ id;
			mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}

	}

}
